# Loading required packages
library(miceadds); library(sandwich); library(stargazer)
# Loading the data
# State level request data
load("state_level_data.RData")
# County level request data
load("county_level_request_data.RData")

#############
# TABLE C.8 #
#############
# First, renaming some variables so they are the same across the two datasets and can be easily put in the same stargazer output table
# State-level variables
colnames(senators_appropriations_state)[colnames(senators_appropriations_state) == "democrat"] <- "dem"
colnames(senators_appropriations_state)[colnames(senators_appropriations_state) == "distance_from_dw_median_scaled"] <- "meddist_scaled"
colnames(senators_appropriations_state)[colnames(senators_appropriations_state) == "POPPCT_URBAN_scaled"] <- "pct_urban_scaled"
colnames(senators_appropriations_state)[colnames(senators_appropriations_state) == "state.median.household.income_scaled"] <- "median_household_income_scaled"

# County-level variables
colnames(senators_appropriations)[colnames(senators_appropriations) == "log_countypop_scaled"] <- "logpop_scaled"
colnames(senators_appropriations)[colnames(senators_appropriations) == "log_sum"] <- "logsum"

# County-level OLS regression, subset to those senators who request
reg_county <- lm(logsum ~ dem + on_appropriations + meddist_scaled + logpop_scaled +  female + 
                   core_county + swing_county + seniority_scaled + party_leader + 
                   freshman + pct_urban_scaled + median_household_income_scaled  + factor(year) + capital + other_sen_requested*other_sen_sameparty,
                 data=senators_appropriations[senators_appropriations$total_sen_sum>0,])
summary(reg_county)

# Same model, clustering by senator
# Making a unique senator variable to cluster with
senators_appropriations$unique_senator_name <- paste0(senators_appropriations$senator, senators_appropriations$state)

reg_county_cluster <- lm.cluster(logsum ~ dem + on_appropriations + meddist_scaled + logpop_scaled +  female + 
                              core_county + swing_county + seniority_scaled + party_leader + 
                              freshman + pct_urban_scaled + median_household_income_scaled  + factor(year) + capital + other_sen_requested*other_sen_sameparty,
                            cluster = "unique_senator_name", data=senators_appropriations[senators_appropriations$total_sen_sum>0,])
summary(reg_county_cluster)

# State-level OLS regression, subset to those senators who request
reg_state <- lm(logsum ~ dem + on_appropriations + meddist_scaled +  logpop_scaled + female + 
                  previous_vote_share_scaled + seniority_scaled + party_leader +
                  freshman + pct_urban_scaled + median_household_income_scaled + factor(year),
                data=senators_appropriations_state[senators_appropriations_state$appropriation_sum>0,])

# Same model, clustering by senator
# Making a unique senator variable to cluster with
senators_appropriations_state$unique_senator_name <- paste0(senators_appropriations_state$senator, senators_appropriations_state$state)

reg_state_cluster <- lm.cluster(logsum ~ dem + on_appropriations + meddist_scaled +  logpop_scaled + female + 
                             previous_vote_share_scaled + seniority_scaled + party_leader +
                             freshman + pct_urban_scaled + median_household_income_scaled + factor(year),
                           cluster = "unique_senator_name", data=senators_appropriations_state[senators_appropriations_state$appropriation_sum>0,])

# Grabbing coefficients and standard errors from the clustered models
cond_cluster  <- data.frame(summary(reg_county_cluster))
cond2_cluster  <- data.frame(summary(reg_state_cluster))

coefs_cluster <- c(cond_cluster[,1])
ses_cluster <- c(cond_cluster[,2])
coefs2_cluster <- c(cond2_cluster[,1])
ses2_cluster <- c(cond2_cluster[,2])

# Save the regression results as a table
# coef and se take the coefficients and standard errors from the clustered models
stargazer(reg_state, reg_county,
          omit=c("Constant"),
          notes.append = FALSE,notes.label = "",
          report="vc*s",star.char=c("*","**"),star.cutoffs = c(0.10,0.05),no.space = TRUE,
          font.size = "footnotesize", model.numbers = FALSE,
          column.labels=c("State Level","County Level"),
          dep.var.labels = "Log(Appropriations Requests + 1)",column.sep.width="0pt",
          covariate.labels=c( "Democrat (Majority Party Member)", "Member of Appropriations Committee","Distance from Floor Median",
                              "Log Population",
                              "Senator is a Woman","Previous Vote Share", "Core County", "Swing County","Seniority", "Party Leader", 
                              "Freshman Senator", "Percent Urban Population", "Median Household Income",
                              "Fiscal Year 2023", "County Includes Capital City", "Other Senator Requested Funds to County","Other Senator is Same Party","Other Senator Requested * Other Senator Same Party"),
          notes="\\parbox[t]{.9\\textwidth}{\\footnotesize \\textit{Note}: The table shows the results from OLS regressions of a
                        senator's state-level (first column) and county-level (second column) appropriation request behavior. Note that these models are limited to senators who actually made a request. For both models,
                        regression standard errors are clustered by senator. $^{**}p<0.05$, $^*p<0.10$. All continuous variables are standardized for ease of comparison.}",
          label="tab1_county_requests_among_requesters",
          digits=3,
          coef=list(coefs2_cluster, coefs_cluster), 
          se=list(ses2_cluster, ses_cluster),
          digits.extra = 0,
          title="Predictors of Spending Requests Among Requesters",
          omit.stat = c("ll","rsq","adj.rsq","ser","f"))
